<?php
/*---------------------------------------------------------------------
 * @function 全局公共函数，多个应用之间公用
 * @author: yangjian
 * @since 2016-11-07
 *-----------------------------------------------------------------------*/
//导出excel文档
function phpexcel($name, $title, $data, $type = 1, $second_title = null, $second_data = null)
{
    $objPHPExcel = new PHPExcel();
    /*以下是一些设置 ，什么作者  标题啊之类的*/
    $objPHPExcel->getProperties()->setCreator('Fiidee.Monda.Cn')
        ->setLastModifiedBy("Fiidee.Monda.Cn")
        ->setTitle("Fiidee.Monda.Cn")
        ->setSubject("Fiidee.Monda.Cn")
        ->setDescription("Fiidee.Monda.Cn")
        ->setKeywords("Fiidee.Monda.Cn")
        ->setCategory("Fiidee.Monda.Cn");
    //以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改
    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()->setTitle($name);
    $objPHPExcel->getActiveSheet()->getColumnDimension()->setAutoSize(true);
    //第一行默认为表头
    //在设置单元表格值的时候，前面加一个空格，可以强转为字符串，避免出现科学计数法
    $row = 1;
    if ($title && is_array($title)) {
        $col = 0;
        foreach ($title as $v) {
            $objPHPExcel->getActiveSheet()->
            setCellValueExplicit(chr($col+65).$row,$v,PHPExcel_Cell_DataType::TYPE_STRING);
            //setCellValueByColumnAndRow($col, $row, " ".$v);
            $col++;
        }
        $row++;
    }
    //表格内容
    foreach ($data as $k => $v) {
        $col = 0;
        foreach ($v as $val) {
            if ($col + 1 > count($title)) continue;
            $objPHPExcel->getActiveSheet()
                ->setCellValueExplicit(chr($col+65).$row,$val,PHPExcel_Cell_DataType::TYPE_STRING);
            //->setCellValueByColumnAndRow($col, $row, " ".$val);
            $col++;
        }
        $row++;
    }
    if ($type == 2) {//多表 应用于财务账单列表
        $num = 1;
        foreach ($second_data as $k => $v) {
            if ($v)
                $objPHPExcel->createSheet();
            $objPHPExcel->setActiveSheetIndex($num);
            $objPHPExcel->getActiveSheet()->setTitle($data[$k]['name'] . '清单');
            //第一行默认为表头
            $row = 1;
            $col = 0;
            if ($second_title && is_array($second_title)) {
                foreach ($second_title as $val) {
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, " ".$val);
                    $col++;
                }
                $row++;
            }
            //表格内容
            foreach ($v as $val) {
                $col = 0;
                foreach ($val as $value) {
                    if ($col + 1 > count($second_title)) continue;
                    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, " ".$value);
                    $col++;
                }
                $row++;
            }
            $num++;
        }
    }

    $name = $name . date("YmdHis");
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="' . $name . '.xls"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
    exit;
}

/**
 * 读取excel文档到数组
 * @param $file
 * @param $header
 * @param $err_msg  错误信息
 * @return array
 */
function excelReader($file, $header, &$err_msg) {

    $phpReader = new PHPExcel_Reader_Excel2007();
    if( !$phpReader->canRead($file) ){
        $phpReader = new PHPExcel_Reader_Excel5();
        if( !$phpReader->canRead($file) ){
            $err_msg = '读取Excel文档失败';
        }
    }
    $phpExcel = $phpReader->load($file);
    /**读取excel文件中的第一个工作表*/
    $sheet = $phpExcel->getSheet(0);

    /**取得最大的列号*/
    $maxCols = $sheet->getHighestColumn();
    //$maxCols = 'V';
    /**取得一共有多少行*/
    $totalRows = $sheet->getHighestRow();

    //读取数据，组成数组
    /**从第二行开始输出，因为excel表中第一行为列名*/
    $start = 2;
    $items = array();
    for($currentRow = $start; $currentRow <= $totalRows; $currentRow++){
        /**从第A列开始输出*/
        $tmp = array();
        for( $currentColumn = 'A'; $currentColumn <= $maxCols; $currentColumn++ ) {
            if (!isset($header[$currentColumn])) continue;
            $tmp[$header[$currentColumn]] = trim($sheet->getCellByColumnAndRow(ord($currentColumn) - 65, $currentRow)->getValue());
        }
        $items[] = $tmp;
    }
    return $items;
}
